﻿    CREATE OR REPLACE FUNCTION find_student(
        name VARCHAR(128) DEFAULT NULL) 
    RETURNS 
    SETOF student 
    AS
    $$
        /***************************************************************************************************
    **  Function:	public.find_student
    **  Author:		Zhixian Ong
    **
    **  Description:
    **    
    **      Search for student with specified name.
    **
    **  Parameters (1):
    **
    **      name    VARCHAR(128)    - name of student to find
    **
    **  Return:
    **
    **      SETOF student
    **
    **  Sample call:
    **
    **      Find student name 
    **      SELECT * FROM find_student('Judy');
    **      SELECT * FROM find_student('');
    **      SELECT * FROM find_student();
    **      SELECT * FROM find_student('zxc');
    **
    **
    **  Modification History:
    **
    **      20140102  Zhixian Ong     - Initial prototype
    ** 
    **  Status:
    **
    **      Dev
    ** 
    ***************************************************************************************************/
    BEGIN
        
        --------------------------------------------------------------------------------
        -- Process parameter variables
        --------------------------------------------------------------------------------
        IF TRIM(name) = '' THEN
            name = NULL;
        ELSE
            name = '%' || name || '%';
        END IF;
        
        --------------------------------------------------------------------------------
        -- Return result
        --------------------------------------------------------------------------------
        RETURN QUERY 
        SELECT  * 
        FROM    student 
        WHERE   (name IS NULL)
                OR
                (
                    (name IS NOT NULL)
                    AND
                    (display_name LIKE name)
                );
    END;
    $$ LANGUAGE plpgsql;